package io.babystar.modules.business.service.impl;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import io.babystar.common.utils.PageUtils;
import io.babystar.common.utils.Query;
import io.babystar.modules.business.dao.StudentDao;
import io.babystar.modules.business.dao.StudentManageDao;
import io.babystar.modules.business.entity.StudentEntity;
import io.babystar.modules.business.entity.StudentManageEntity;
import io.babystar.modules.business.service.StudentManageService;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import static org.apache.poi.ss.usermodel.Cell.*;


@Service("studentManageService")
public class StudentManageServiceImpl extends ServiceImpl<StudentManageDao, StudentManageEntity> implements StudentManageService {
    @Autowired
    StudentManageDao studentManageDao;
    @Autowired
    StudentDao studentDao;

    @Override
    public PageUtils queryPage(Map<String, Object> params) {
        /*IPage<StudentManageEntity> page = this.page(
                new Query<StudentManageEntity>().getPage(params),
                new QueryWrapper<StudentManageEntity>()
        );*/
        Page<StudentManageEntity> page = new Page<>();
        page.setTotal(studentManageDao.queryByPageCount(params));
        Integer pageNumber = Integer.parseInt(params.get("page").toString());
        Integer pageSize = Integer.parseInt(params.get("limit").toString());
        page.setCurrent(pageNumber);
        page.setSize(pageSize);
        Long current = page.getCurrent();
        Long size = page.getSize();
        Long currentStart = size*current - size;
        params.put("currentStart",currentStart);
        params.put("size",size);
        page.setRecords(studentManageDao.queryByPage(params));
        return new PageUtils(page);
    }

    @Override
    public List<StudentManageEntity> queryAll() {
        return studentManageDao.queryAll();
    }

    @Override
    public List<StudentManageEntity> add(MultipartFile file) throws Exception {
        //存放excel表中所有信息
        List<StudentManageEntity> studentManageList = new ArrayList<>();
        /**
         *
         * 判断文件版本
         */
        String fileName = file.getOriginalFilename();
        String suffix = fileName.substring(fileName.lastIndexOf(".")+1);

        InputStream ins = file.getInputStream();

        Workbook wb = null;

        if(suffix.equals("xlsx")) {
            //XSSFWorkbook:是操作Excel2007的版本，扩展名是.xlsx
            wb = new XSSFWorkbook(ins);
        }else if(suffix.equals("xls")) {
            //HSSFWorkbook:是操作Excel2003以前（包括2003）的版本，扩展名是.xls
            wb = new HSSFWorkbook(ins);
        }else {
            return null;
        }
        /**
         * 获取excel表单
         */
        Sheet sheet = wb.getSheetAt(0);

        /**
         * line = 1 :从表的第二行开始获取记录
         *
         */
        if(null != sheet){

            for(int line = 1; line <= sheet.getLastRowNum();line++){

                StudentManageEntity studentManageEntity = new StudentManageEntity();

                Row row = sheet.getRow(line);

                if(null == row){
                    continue;
                }

                /**
                 * 获取单元格的内容
                 */
                String studentName = getValue(row.getCell(0));

                String studentStage = getValue(row.getCell(1));

                String listeningIntentionality = getValue(row.getCell(2));

                String sourceChannel = getValue(row.getCell(3));

                String followStatus = getValue(row.getCell(4));

                String followTime = getValue(row.getCell(5));

                String classroomStatus = getValue(row.getCell(6));

                String attendanceStatus = getValue(row.getCell(7));

                String isUnsubscribe = getValue(row.getCell(8));

                String auditionTime = getValue(row.getCell(9));

                String appointmentPerson = getValue(row.getCell(10));

                String appointmentTime = getValue(row.getCell(11));

                String registrationIntention = getValue(row.getCell(12));

                String studentType = getValue(row.getCell(13));

                String studentStatus = getValue(row.getCell(14));

                String coachTeacher = getValue(row.getCell(15));

                String renewalIntention = getValue(row.getCell(16));

                String introductionIntention = getValue(row.getCell(17));

                String curriculaTime = getValue(row.getCell(18));

                String createAt = getValue(row.getCell(19));

                SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                List<StudentEntity> studentEntityList = studentDao.selectList(new QueryWrapper<StudentEntity>().eq("student_name", studentName));
                if(null != studentEntityList && studentEntityList.size() > 0){
                    studentManageEntity.setStudentId(studentEntityList.get(0).getId());
                }
                if(null != studentName){
                    studentManageEntity.setStudentName(studentName);
                }
                if(null != studentStage){
                    studentManageEntity.setStudentStage(Integer.parseInt(studentStage));
                }
                if(null != listeningIntentionality){
                    studentManageEntity.setListeningIntentionality(Integer.parseInt(listeningIntentionality));
                }
                if(null != sourceChannel){
                    studentManageEntity.setSourceChannel(Integer.parseInt(sourceChannel));
                }
                if(null != followStatus){
                    studentManageEntity.setFollowStatus(Integer.parseInt(followStatus));
                }
                if(null != followTime){
                    studentManageEntity.setFollowTime(dateFormat.parse(followTime));
                }
                if(null != classroomStatus){
                    studentManageEntity.setClassroomStatus(Integer.parseInt(classroomStatus));
                }
                if(null != attendanceStatus){
                    studentManageEntity.setAttendanceStatus(Integer.parseInt(attendanceStatus));
                }
                if(null != isUnsubscribe){
                    studentManageEntity.setIsUnsubscribe(Integer.parseInt(isUnsubscribe));
                }
                if(null != auditionTime){
                    studentManageEntity.setAuditionTime(dateFormat.parse(auditionTime));
                }
                if(null != appointmentPerson){
                    studentManageEntity.setAppointmentPerson(appointmentPerson);
                }
                if(null != appointmentTime){
                    studentManageEntity.setAppointmentTime(dateFormat.parse(appointmentTime));
                }
                if(null != registrationIntention){
                    studentManageEntity.setRegistrationIntention(Integer.parseInt(registrationIntention));
                }
                if(null != studentType){
                    studentManageEntity.setStudentType(Integer.parseInt(studentType));
                }
                if(null != studentStatus){
                    studentManageEntity.setStudentStatus(Integer.parseInt(studentStatus));
                }
                if(null != coachTeacher){
                    studentManageEntity.setCoachTeacher(coachTeacher);
                }
                if(null != renewalIntention){
                    studentManageEntity.setRenewalIntention(Integer.parseInt(renewalIntention));
                }
                if(null != introductionIntention){
                    studentManageEntity.setIntroductionIntention(Integer.parseInt(introductionIntention));
                }
                if(null != curriculaTime){
                    studentManageEntity.setCurriculaTime(dateFormat.parse(curriculaTime));
                }
                if(null != createAt){
                    studentManageEntity.setCreateAt(dateFormat.parse(createAt));
                }
                studentManageList.add(studentManageEntity);
            }

            for (StudentManageEntity studentManageEntity : studentManageList) {
                /**
                 * 判断数据库表中是否存在用户记录，若存在，则更新，不存在，则保存记录
                 */
                List<StudentManageEntity> studentManageEntities = studentManageDao.selectList(new QueryWrapper<StudentManageEntity>().eq("student_id", studentManageEntity.getStudentId()));
                if(null != studentManageEntities && studentManageEntities.size() > 0){
                    studentManageEntity.setId(studentManageEntities.get(0).getId());
                    studentManageDao.updateById(studentManageEntity);
                }else{
                    studentManageDao.insert(studentManageEntity);
                }
            }
        }
        return studentManageList;
    }
    private static String getValue(Cell cell) {
        String returnValue = null;
        if(null != cell){
            switch (cell.getCellType()) {
                case CELL_TYPE_NUMERIC:   //数字
                    //判断是否日期
                    if(HSSFDateUtil.isCellDateFormatted(cell)){
                        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                        returnValue = dateFormat.format(cell.getDateCellValue());
                    }else {
                        Double doubleValue = cell.getNumericCellValue();
                        // 格式化科学计数法，取一位整数
                        DecimalFormat df = new DecimalFormat("0");
                        returnValue = df.format(doubleValue);
                    }
                    break;
                case CELL_TYPE_STRING:    //字符串
                    returnValue = cell.getStringCellValue();
                    break;
                case CELL_TYPE_BOOLEAN:   //布尔
                    Boolean booleanValue = cell.getBooleanCellValue();
                    returnValue = booleanValue.toString();
                    break;
                case CELL_TYPE_BLANK:     // 空值
                    break;
                case CELL_TYPE_FORMULA:   // 公式
                    returnValue = cell.getCellFormula();
                    break;
                case CELL_TYPE_ERROR:     // 故障
                    break;
                default:
                    break;
            }
        }
        return returnValue;
    }
}